package com.pf.export;

import com.google.common.collect.Lists;
import com.pf.utils.ExportExcelUtils;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.util.List;

/**
 * 下拉列表导出
 * <p>
 * 1.固定下拉列表（下拉选项不能超过255字符，否则会报错）
 * 2.无限下拉列表（利用隐藏sheet页实现下拉列表，选项可以超过255字符）
 * <p>
 * 参考博客：https://blog.csdn.net/q1054733797/article/details/126051548
 *
 * @author PanFei
 * @version 1.0.0
 * @createTime 2023/3/4 16:54
 */
public class DropDownListDemo {

    public static void main(String[] args) throws Exception {
        // 测试导出有限的下拉列表
        createBoundedDropDownList();
        // 测试导出的无限的下拉列表
        createUnlimitedDropDownList();

        createUnlimitedDropDownList2();
    }

    /**
     * 创建有限的下拉列表（下拉列表选项不能超过255字符，否则会报错）
     */
    public static void createBoundedDropDownList() throws Exception {

        // 设置下拉框数据(如果下拉选项超过255字符，会报错，报错信息如下：A valid formula or a list of values must be less than or equal to 255 characters (including separators).)
        // 1.模拟下拉选项值
        int size = 20;
        List<String> optionList = Lists.newArrayList();
        for (int i = 0; i < size; i++) {
            optionList.add("选项" + i);
        }

        // 2.创建excel工作薄和sheet对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet");
        // 创建表头
        XSSFRow headRow = sheet.createRow(0);
        headRow.createCell(0).setCellValue("有界的下拉列表");
        sheet.setDefaultColumnWidth(25);

        ExportExcelUtils.createBoundedDropDownList(sheet, 0, optionList);
        // 4.输出excel
        workbook.write(new FileOutputStream("D:/test/boundedDropDownList.xlsx"));
        System.out.println(">>>> 导出成功...");
    }

    /**
     * 创建下拉列表（下拉选项可以无限）
     * <p>
     * 隐藏sheet页数据显示在一列
     *
     * @throws Exception
     */
    public static void createUnlimitedDropDownList() throws Exception {

        // 1.创建下拉列表选项
        int size = 200;
        String[] listOfValues = new String[size];
        for (int i = 0; i < size; i++) {
            listOfValues[i] = "选项" + i;
        }

        // 2.创建excel工作薄对象和sheet对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet");
        // 创建表头
        XSSFRow headRow = sheet.createRow(0);
        headRow.createCell(0).setCellValue("无界的下拉列表");
        sheet.setDefaultColumnWidth(25);

        // 3.创建隐藏sheet页，并将下拉选项的选项值存储在隐藏sheet页中（选项存储在excel的第1列）
        XSSFSheet hiddenSheet = workbook.createSheet("hiddenSheet");
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
        for (int i = 0; i < listOfValues.length; i++) {
            XSSFRow hiddenRow = hiddenSheet.createRow(i);
            XSSFCell hiddenCell = hiddenRow.createCell(0);
            hiddenCell.setCellValue(listOfValues[i]);
        }

        // 4.创建下拉列表，选项值引用隐藏sheet页（hiddenSheet）的A列数据
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 设置下拉框数据引用
        // "sheet名字!$A$1:$A$100" ：由sheet页名称，起始列，起始行，结束列，结束行
        DataValidationConstraint constraint = helper.createFormulaListConstraint("hiddenSheet!$A$1:$A$" + listOfValues.length);
        // 设置生效的起始行，终止行，起始列，终止列
        CellRangeAddressList addressList = new CellRangeAddressList(0, 100, 0, 0);
        DataValidation validation = helper.createValidation(constraint, addressList);
        // 适配xls和xlsx
        if (validation instanceof HSSFDataValidation) {
            validation.setSuppressDropDownArrow(false);
        } else {
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
        }
        sheet.addValidationData(validation);
        workbook.write(new FileOutputStream("D:/test/unlimitedDropDownList.xlsx"));
        System.out.println(">>>> 导出成功...");
    }

    /**
     * 创建下拉列表（下拉选项可以无限）
     * <p>
     * 隐藏sheet页数据显示在一行
     *
     * @throws Exception
     */
    public static void createUnlimitedDropDownList2() throws Exception {
        // 1.准备下拉选项数据
        int size = 20;
        List<String> optionList = Lists.newArrayList();
        for (int i = 0; i < size; i++) {
            optionList.add("选项" + i);
        }

        // 2.创建excel工作薄对象和sheet对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        String businessSheetName = "sheet";
        XSSFSheet sheet = workbook.createSheet(businessSheetName);
        // 创建表头
        XSSFRow headRow = sheet.createRow(0);
        headRow.createCell(0).setCellValue("无界的下拉列表");
        sheet.setDefaultColumnWidth(25);

        ExportExcelUtils.createUnlimitedDropDownList(workbook, businessSheetName, 0, 0, optionList);
        // 3.导出excel
        workbook.write(new FileOutputStream("D:/test/unlimitedDropDownList2.xlsx"));
        System.out.println(">>>> 导出成功...");
    }

}
